package com.wanmait.movies.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.wanmait.movies.util.DBHelper;
import com.wanmait.movies.vo.Director;

public class DirectorDao {

	private DirectorDao() {};
	private static DirectorDao directorDao = new DirectorDao();
	public static DirectorDao getDao() {
		
		return directorDao;
	}
	//获得总数
	 public int getAllCount(){
			
			Connection conn = DBHelper.getDBHelper().getconn();
			PreparedStatement ps =null;
			ResultSet rs =null;
			
			String sql = "select count(id) as c from director";
			try {
				ps =conn.prepareStatement(sql);
				rs=ps.executeQuery();
				rs.next();
				
				int i = rs.getInt("c");
				
				return i;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DBHelper.getDBHelper().close(rs, ps, conn);
			}
			return 0;
	   }
	
	//获得导演分页列表
		public ArrayList<Director> getDirectorList(int i){
			ArrayList<Director> list =new ArrayList<Director>();
			
			Connection conn = DBHelper.getDBHelper().getconn();
			PreparedStatement ps =null;
			ResultSet rs =null;
			
			String sql ="select * from director limit ?,8";
			
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1, i*8);
				rs = ps.executeQuery();
				
				while(rs.next()) {
					Director director =new Director();
					director.setId(rs.getInt("id"));
					director.setDirectorName(rs.getString("director_name"));
					director.setDirectorImage(rs.getString("director_image"));
					
					list.add(director);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DBHelper.getDBHelper().close(rs, ps, conn);
			}
			return list;
		}
		
	 
	//获得导演列表
	public ArrayList<Director> getDirectorList(){
		ArrayList<Director> list =new ArrayList<Director>();
		
		Connection conn = DBHelper.getDBHelper().getconn();
		PreparedStatement ps =null;
		ResultSet rs =null;
		
		String sql ="select * from director limit 0,8";
		
		try {
			ps = conn.prepareStatement(sql);
			
			rs = ps.executeQuery();
			
			while(rs.next()) {
				Director director =new Director();
				director.setId(rs.getInt("id"));
				director.setDirectorName(rs.getString("director_name"));
				director.setDirectorImage(rs.getString("director_image"));
				
				list.add(director);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return list;
	}
	
	//通过姓名查找导演
	
	public ArrayList<Director> findByName(String name){
		
		ArrayList<Director> lists =new ArrayList<Director>();
		Connection conn = DBHelper.getDBHelper().getconn();
		PreparedStatement ps =null;
		ResultSet rs =null;
		
		String sql = "select * from director where director_name like '%"+name+"%'";
		try {
			ps =conn.prepareStatement(sql);
			
			rs=ps.executeQuery();
			
			while(rs.next()) {
				Director director =new Director();
				director.setId(rs.getInt("id"));
				director.setDirectorName(rs.getString("director_name"));
				director.setDirectorImage(rs.getString("director_image"));
				
				lists.add(director);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return lists;
	}
	
	//添加新导演
   public void addByName(String name,String image,String intro){
		
		Connection conn = DBHelper.getDBHelper().getconn();
		PreparedStatement ps =null;
		ResultSet rs =null;
		
		String sql = "insert into director(director_name,director_image,director_introduce) values(?,?,?)";
		try {
			ps =conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.setString(2, image);
			ps.setString(3, intro);
			ps.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		
   }
   
 //通过id获得导演
   public Director findById(int id){
		
		Connection conn = DBHelper.getDBHelper().getconn();
		PreparedStatement ps =null;
		ResultSet rs =null;
		
		String sql = "select * from director where id=?";
		try {
			ps =conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			if(rs.next()) {
			Director d= new Director();
			d.setDirectorImage(rs.getString("director_image"));
			d.setDirectorName(rs.getString("director_name"));
			d.setId(id);
			d.setDirectorIntroduce(rs.getString("director_introduce"));
			return d;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return null;
   }
 //删除导演
   public void deleteById(int id){
		
		Connection conn = DBHelper.getDBHelper().getconn();
		PreparedStatement ps =null;
		
		String sql = "delete from director where id=?";
		try {
			ps =conn.prepareStatement(sql);
			ps.setInt(1, id);
		
			ps.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(null, ps, conn);
		}

   }
 //修改导演
 	 public void updateDirector(String name,String introduce,int id){
 			
 			Connection conn = DBHelper.getDBHelper().getconn();
 			PreparedStatement ps =null;
 			
 			String sql = "UPDATE director set director_name=?,director_introduce=? where id=? ";
 			try {
 				ps =conn.prepareStatement(sql);
 				ps.setString(1, name);
 				ps.setString(2, introduce);
 				ps.setInt(3, id);
 			
 				ps.executeUpdate();
 				
 			} catch (SQLException e) {
 				// TODO Auto-generated catch block
 				e.printStackTrace();
 			}
 			finally {
 				DBHelper.getDBHelper().close(null, ps, conn);
 			}

 	   }
 	//修改演员
 		 public void updateDirector(String name,String introduce,String image,int id){
 				
 				Connection conn = DBHelper.getDBHelper().getconn();
 				PreparedStatement ps =null;
 				
 				String sql = "UPDATE director set director_name=?,director_image=?,director_introduce=? where id=? ";
 				try {
 					ps =conn.prepareStatement(sql);
 					ps.setString(1, name);
 					ps.setString(2, image);
 					ps.setString(3, introduce);
 					ps.setInt(4, id);
 				
 					ps.executeUpdate();
 					
 				} catch (SQLException e) {
 					// TODO Auto-generated catch block
 					e.printStackTrace();
 				}
 				finally {
 					DBHelper.getDBHelper().close(null, ps, conn);
 				}

 		   }
 		   
}
